﻿using BCCommon;
using BCEntity.TMS.Contraband.ContrabandConfig;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BCData.TMS.Contraband.ContrabandConfig
{
    public class ContrabandConfigData : IContrabandConfigData
    {
        private readonly IDatabaseContext database;

        private const string QUERY = @"SELECT
		                                A.ContrabandConfigId,
		                                A.ContrabandCategoryId,
		                                A.ContrabandContent,
		                                A.CreateAdminId,
		                                A.CreateAdminName,
		                                A.CreateTime,
		                                A.UpdateTime,
		                                A.EditAdminId,
		                                A.EditAdminName 
                                    FROM
                                        ContrabandConfig A";

        private const string QUERY_JOIN = @"SELECT
		                                A.ContrabandConfigId,
		                                A.ContrabandCategoryId,
		                                A.ContrabandContent,
		                                A.CreateAdminId,
		                                A.CreateAdminName,
		                                A.CreateTime,
		                                A.UpdateTime,
		                                A.EditAdminId,
		                                A.EditAdminName,
                                        B.ContrabandCategory 
                                    FROM
                                        ContrabandConfig A
                                        INNER JOIN ContrabandCategory B ON A.ContrabandCategoryId = B.ContrabandCategoryId";

        #region 构造函数
        public ContrabandConfigData(IDatabaseContext database)
        {
            this.database = database;
        }
        #endregion

        #region InsertAsync
        /// <summary>
        /// 添加新违禁品配置
        /// </summary>
        /// <param name="entity">违禁品配置信息</param>
        /// <returns></returns>
        public async Task<ContrabandConfigEntity> InsertAsync(ContrabandConfigEntity entity)
        {
            return await this.database.Master.InsertAsync(entity);
        }
        #endregion

        #region updateAsync
        /// <summary>
        /// 修改违禁品配置记录
        /// </summary>
        /// <param name="entity">违禁品配置信息</param>
        /// <returns></returns>
        public async Task<ContrabandConfigEntity> UpdateAsync(ContrabandConfigEntity entity)
        {
            return await this.database.Master.UpdateAsync(entity);
        }
        #endregion 

        #region Delete
        /// <summary>
        /// 删除违禁品配置记录
        /// </summary>
        /// <param name="contrabandConfigId">违禁品配置Id</param>
        /// <returns></returns>
        public async Task<int> DeleteAsync(int contrabandConfigId)
        {
            return await this.database.Master.ExecuteAsync("DELETE FROM ContrabandConfig WHERE ContrabandConfigId = ?ContrabandConfigId",new { contrabandConfigId });
        }
        #endregion

        #region GetById
        /// <summary>
        /// 获取违禁品配置
        /// </summary>
        /// <param name="contrabandConfigId">违禁品配置Id</param>
        /// <returns></returns>
        public async Task<ContrabandConfigEntity> GetEntityAsync(int contrabandConfigId)
        {
            return await this.database.Slave.QueryEntityAsync<ContrabandConfigEntity>($"{QUERY} WHERE A.ContrabandConfigId = ?ContrabandConfigId", new { contrabandConfigId });
        }
        #endregion

        #region GetListAsync 查询违禁品配置记录
        public async Task<(IEnumerable<ContrabandConfigViewEntity>, int)> GetListAsync(int pageIndex, int pageSize)
        {

            DynamicParameters parameters = new DynamicParameters();
            //查询条件
            List<string> wheres = new List<string>();


            string sql = string.Format(@"{0} {1} ; SELECT COUNT(1) FROM  contrabandconfig  {1}",
                   QUERY,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize
                  );

            return await this.database.QueryMultipleAsync<ContrabandConfigViewEntity, int>(sql, parameters);
        }
        #endregion

        public async Task<IEnumerable<ContrabandConfigEntity>> GetByContrabandCategoryId(int contrabandCategoryId)
        {
            return await database.Slave.QueryAsync<ContrabandConfigEntity>($"{QUERY} WHERE A.ContrabandCategoryId = ?ContrabandCategoryId", new { contrabandCategoryId });
        }

        public async Task<(IEnumerable<ContrabandConfigViewEntity>, int)> Query(int? contrabandCategoryId, DateTime? createStartDate, DateTime? createEndDate, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (contrabandCategoryId.HasValue)
            {
                wheres.Add("A.ContrabandCategoryId = ?ContrabandCategoryId");
                parameters.Add("?ContrabandCategoryId", contrabandCategoryId.Value, DbType.Int32);
            }
            if (createStartDate.HasValue)
            {
                wheres.Add("A.CreateTime >= ?CreateStartDate");
                parameters.Add("?CreateStartDate", createStartDate.Value.Date, DbType.Date);
            }
            if (createEndDate.HasValue)
            {
                wheres.Add("A.CreateTime < ?CreateEndDate");
                parameters.Add("?CreateEndDate", createEndDate.Value.AddDays(1).Date, DbType.Date);
            }
            string sql = string.Format(@"{0} {1} ORDER BY A.ContrabandConfigId DESC LIMIT {2},{3};
                                    SELECT COUNT(1) FROM ContrabandConfig A {1}",
                                    QUERY_JOIN,
                                    wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty,
                                    (pageIndex - 1) * pageSize,
                                    pageSize);
            return await database.Slave.QueryPageableListAsync<ContrabandConfigViewEntity>(sql, parameters);
        }

        public async Task<IEnumerable<ContrabandConfigViewEntity>> GetList(int? contrabandCategoryId)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (contrabandCategoryId.HasValue)
            {
                wheres.Add("A.ContrabandCategoryId = ?ContrabandCategoryId");
                parameters.Add("?ContrabandCategoryId", contrabandCategoryId.Value, DbType.Int32);
            }
            string sql = string.Format("{0} {1} ORDER BY A.ContrabandConfigId DESC",
                                    QUERY_JOIN,
                                    wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty);
            return await database.Slave.QueryAsync<ContrabandConfigViewEntity>(sql, parameters);
        }
    }
}
